{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12.13451011  8.54382539 14.57340193 ... 11.91195671 13.77154479\n",
      " 12.6343433 ]\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import math\n",
    "# from sklearn import ensemble\n",
    "# from sklearn import datasets\n",
    "# from sklearn.utils import shuffle\n",
    "# from sklearn.metrics import mean_squared_error\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import accuracy_score\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import BaggingClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier    \n",
    "from sklearn.ensemble import AdaBoostClassifier\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "from sklearn.ensemble import GradientBoostingRegressor\n",
    "#导入GridSearch\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "#使用随机森林作为模型\n",
    "\n",
    "'''\n",
    "movie_id\n",
    "keyword_id\n",
    "id\n",
    "info_type_id\n",
    "role_id\n",
    "person_id\n",
    "company_type_id\n",
    "company_id\n",
    "production_year\n",
    "kind_id\n",
    "'''\n",
    "Cnt = 0;\n",
    "\n",
    "Dict_table = {}\n",
    "Dict_table['t'] = 0\n",
    "Dict_table['mc'] = 1\n",
    "Dict_table['ci'] = 2\n",
    "Dict_table['mi'] = 3\n",
    "Dict_table['mi_idx'] = 4\n",
    "Dict_table['mk'] = 5\n",
    "\n",
    "Dict_attr = {}\n",
    "Dict_attr['t.production_year'] = 0\n",
    "Dict_attr['mi.info_type_id'] = 1\n",
    "Dict_attr['t.kind_id'] = 2\n",
    "Dict_attr['mi_idx.info_type_id'] = 3\n",
    "Dict_attr['mk.keyword_id'] = 4\n",
    "Dict_attr['ci.person_id'] = 5\n",
    "Dict_attr['mc.company_id'] = 6\n",
    "Dict_attr['mc.company_type_id'] = 7\n",
    "Dict_attr['ci.role_id'] = 8\n",
    "\n",
    "Dict_join = {}\n",
    "Dict_join[''] = 0\n",
    "Dict_join['t.id=mc.movie_id'] = 1\n",
    "Dict_join['t.id=mi.movie_id'] = 2\n",
    "Dict_join['t.id=mi_idx.movie_id'] = 3\n",
    "Dict_join['t.id=ci.movie_id'] = 4\n",
    "Dict_join['t.id=mk.movie_id'] = 5\n",
    "\n",
    "# t.production_year\n",
    "# mi.info_type_id\n",
    "# t.kind_id\n",
    "# mi_idx.info_type_id\n",
    "# mk.keyword_id\n",
    "# ci.person_id\n",
    "# mc.company_id\n",
    "# mc.company_type_id\n",
    "# ci.role_id\n",
    "\n",
    "Dict_comp = {}\n",
    "Dict_comp['>'] = 0\n",
    "Dict_comp['='] = 1\n",
    "Dict_comp['<'] = 2\n",
    "\n",
    "Dict_max = {}\n",
    "Dict_min = {}\n",
    "\n",
    "with open('column_min_max_vals.csv', 'r') as csvfile:\n",
    "    Max_list = csv.reader(csvfile)\n",
    "    for Max_data in Max_list:\n",
    "        if (Max_data[0] == \"name\"):\n",
    "            continue\n",
    "        Dict_max[Max_data[0]] = Max_data[2]\n",
    "        Dict_min[Max_data[0]] = Max_data[1]\n",
    "\n",
    "# def Getkey(element):\n",
    "#     return Dict_table[element[0].split('.')[0]];\n",
    "# # data load and feature abstract\n",
    "\n",
    "def Parse(Data, Is_train):\n",
    "    \n",
    "    Feature_vec = []\n",
    "    Feature_target = []\n",
    "    \n",
    "    for Sql in Data:\n",
    "        #\n",
    "        Table_name = []\n",
    "        Join_table = []\n",
    "        Condition = []\n",
    "        Target = 0\n",
    "#         Cnt = Cnt + 1\n",
    "#         if (Cnt > 10):\n",
    "#             break\n",
    "        Table_name = Sql[0].split(',')\n",
    "        Join_table = Sql[1].split(',')\n",
    "\n",
    "        Cur_cond = Sql[2].split(',')\n",
    "        Cur_Size = len(Cur_cond)\n",
    "        Cur_Cnt = 0\n",
    "        while (Cur_Cnt < Cur_Size):\n",
    "            Cur_list = []\n",
    "            Cur_list.append(Cur_cond[Cur_Cnt])\n",
    "            Cur_list.append(Cur_cond[Cur_Cnt+1])\n",
    "            Cur_list.append(Cur_cond[Cur_Cnt+2])\n",
    "            Condition.append(Cur_list)\n",
    "            Cur_Cnt = Cur_Cnt + 3\n",
    "        \n",
    "        if (Is_train == 1):\n",
    "            Target = int(Sql[3])\n",
    "\n",
    "        # Create vec\n",
    "        Cur_vec = []\n",
    "        for i in range(54):\n",
    "            Cur_vec.append(0.0)\n",
    "        \n",
    "        pos = 0\n",
    "        for Cur_table_name in Table_name:\n",
    "            Cur_vec[Dict_table[Cur_table_name.split(' ')[-1]]] = 1.0\n",
    "        pos = pos + 6\n",
    "\n",
    "        for Cur_join in Join_table:\n",
    "            Cur_vec[Dict_join[Cur_join] + pos] = 1\n",
    "            Cur_vec[Dict_join[Cur_join]+ pos] = 1\n",
    "            pos = pos + 6\n",
    "        \n",
    "        for i in Condition:\n",
    "            offset = Dict_attr[i[0]] * 4;\n",
    "            Cur_vec[pos + offset + Dict_comp[i[1]]] = 1.0\n",
    "            Cur_vec[pos + offset + 3] = (1.0 * int(i[2]) - 1.0* int(Dict_min[i[0]]) ) / (1.0 * int(Dict_max[i[0]]) - 1.0 * int(Dict_min[i[0]]))\n",
    "        \n",
    "#         if (Is_train == 0):\n",
    "#             print (pos)\n",
    "#             print (Sql)\n",
    "#             print (Cur_vec)\n",
    "#             print(\"\")\n",
    "        Feature_vec.append(Cur_vec)\n",
    "        if (Is_train == 1):\n",
    "            Feature_target.append(math.log(1.0*Target))\n",
    "#     print (Feature_target)\n",
    "    return [Feature_vec, Feature_target]\n",
    "\n",
    "X_Data = []\n",
    "Y_Data = []\n",
    "\n",
    "with open('train.csv', 'r') as csvfile:\n",
    "    Data = csv.reader(csvfile, delimiter = '#')\n",
    "    X_Data = Parse(Data, 1)\n",
    "\n",
    "with open('test_without_label.csv', 'r') as csvfile:\n",
    "    Data = csv.reader(csvfile, delimiter = '#')\n",
    "    Y_Data = Parse(Data, 0)\n",
    "\n",
    "X_train = X_Data[0]\n",
    "Y_train = X_Data[1]\n",
    "\n",
    "X_test = Y_Data[0]\n",
    "\n",
    "weight = []\n",
    "for i in range(18):\n",
    "    weight.append(10)\n",
    "for i in range(40):\n",
    "    if (i % 4 == 3):\n",
    "        weight.append(1)\n",
    "    else:\n",
    "        weight.append(10)\n",
    "        \n",
    "params = {'n_estimators': 500, 'max_depth': 4, 'learning_rate': 0.01}\n",
    "# clf = RandomForestClassifier(n_estimators=500, max_leaf_nodes=16,random_state=42)\n",
    "# clf = ensemble.GradientBoostingRegressor(**params) \n",
    "clf = GradientBoostingRegressor(\n",
    "  loss='ls'\n",
    ", learning_rate=0.1\n",
    ", n_estimators=1000\n",
    "# , subsample=1\n",
    "# , min_samples_split=2\n",
    "# , min_samples_leaf=1\n",
    ", max_depth=7\n",
    "# , init=None\n",
    "# , random_state=None\n",
    "# , max_features=None\n",
    "# , alpha=0.9\n",
    "# , verbose=0\n",
    "# , max_leaf_nodes=None\n",
    "# , warm_start=False\n",
    ")\n",
    "# Cur_search = GridSearchCV(clf, param_grid=cv_params , scoring='r2', iid=False, n_jobs=-1, cv=5)\n",
    "\n",
    "clf.fit(X_train, Y_train)\n",
    "\n",
    "Y_test = clf.predict(X_test)\n",
    "print (Y_test)\n",
    "\n",
    "Writeline = []\n",
    "Cur_Len = len(Y_test)\n",
    "for i in range(Cur_Len):\n",
    "    Writeline.append([i, int(math.exp(Y_test[i]))])\n",
    "\n",
    "with open(\"2019201412.csv\", \"w\", newline = '') as csvfile:\n",
    "    csv_writer = csv.writer(csvfile)\n",
    "    csv_writer.writerow(['Query ID', 'Predicted Cardinality'])\n",
    "    for i in Writeline:\n",
    "        csv_writer.writerow(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
